import java.io.*;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.*;

public class FileConvert {
	
	public static void convertExcelToText(String excelFile, String textFile) throws FileNotFoundException, IOException{
		String text = "";
		String temp = "";
		int row = 0;
		int column = 1;
		
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFile));
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow rown = sheet.getRow(row);
		HSSFCell cell = rown.getCell(column);
		
		do{
			//reads all possible questions
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			if (!temp.equals("#")) text = text + temp + "\n";
			column++;
		} while (!temp.equals("#"));
		
		row = 1;
		column = 0;
		temp = "";
		text = text + "################################" + "\n";
		
		do{
			//reads all the possible answers
			rown = sheet.getRow(row);
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			if (!temp.equals("#"))text = text + temp + "\n";
			row++;
		} while (!temp.equals("#"));
		
		row = 1;
		column = 1;
		temp = "";
		text = text + "################################" + "\n";
		rown = sheet.getRow(row);
		
		do{
			//reads all the question/answer combinations
			column = 1;
			cell = rown.getCell(column);
			
			do{
				cell = rown.getCell(column);
				temp = cell.getStringCellValue();
				if (!temp.equals("#")) text = text + temp + " ";
				column++;
			}while (!temp.equals("#"));
			
			row++;
			column = 0;
			rown = sheet.getRow(row);
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			if (!temp.equals("#"))text = text + "\n";
			
		} while (!temp.equals("#"));
		
		text = text + "\n" + "#####################";
		
		try {
            BufferedWriter out = new BufferedWriter(new FileWriter(textFile));
            out.write(text);
            out.close();
        }
        catch (IOException e)
        {
        	System.out.println("I failed!!!");
            System.out.println("Exception ");       
        }
	}
	
	public static void convertTextToExcel(String textFile, String excelFile) throws IOException{
		FileInputStream inStream = new FileInputStream(textFile);
		DataInputStream dataStream = new DataInputStream(inStream);
		BufferedReader br = new BufferedReader(new InputStreamReader(dataStream));
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sheet 1");
		int row = 0;
		int column = 1;
		HSSFRow rown = sheet.createRow(row);
		HSSFCell cell = rown.createCell(column);
		
		String currentLine;
		
		do {
			currentLine = br.readLine();
			if (!currentLine.endsWith("###")){
				cell.setCellValue(currentLine);
				column += 1;
				cell = rown.createCell(column);
			} else {cell.setCellValue("#");}
		} while(!currentLine.endsWith("###"));
		
		column = 0;
		row = 1;
		rown = sheet.createRow(row);
		cell = rown.createCell(column);
		
		do {
			currentLine = br.readLine();
			if (!currentLine.endsWith("###")){
				cell.setCellValue(currentLine);
				row += 1;
				rown = sheet.createRow(row);
				cell = rown.createCell(column);
			} else{cell.setCellValue("#");}
		} while(!currentLine.endsWith("###"));
		
		column = 1;
		row = 1;
		rown = sheet.getRow(row);
		cell = rown.createCell(column);
		
		do {
			currentLine = br.readLine();
			if (!currentLine.endsWith("###")){
				String[] answers = currentLine.split(" ");
				for (int i = 0; i < answers.length; i++){
					cell.setCellValue(answers[i]);
					column += 1;
					cell = rown.createCell(column);
				}
				cell.setCellValue("#");
			}
			if (!currentLine.endsWith("###")){
				row += 1;
				column = 1;
				rown = sheet.getRow(row);
				cell = rown.createCell(column);
			}
		} while (!currentLine.endsWith("###"));
		
		workbook.write(new FileOutputStream(excelFile));
	}
	
	public static QuestionData convertExcelToQData(String excelFile) throws FileNotFoundException, IOException{
		
		QuestionData qData = new QuestionData();
		
		String temp = "";
		int row = 0;
		int column = 1;
		
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFile));
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow rown = sheet.getRow(row);
		HSSFCell cell = rown.getCell(column);
		
		do{
			//reads all possible questions
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			if (!temp.equals("#")) qData.questions.add(temp);
			column++;
		} while (!temp.equals("#"));
		
		row = 1;
		column = 0;
		temp = "";
		
		do{
			//reads all the possible answers
			rown = sheet.getRow(row);
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			if (!temp.equals("#"))qData.answers.add(temp);
			row++;
		} while (!temp.equals("#"));
		
		row = 1;
		column = 1;
		temp = "";
		rown = sheet.getRow(row);
		int index = 0;
		do{
			qData.experience.add(new ArrayList<Asker.Answer>());
			//reads all the question/answer combinations
			column = 1;
			cell = rown.getCell(column);
			
			do{
				cell = rown.getCell(column);
				temp = cell.getStringCellValue();
				if (!temp.equals("#")) qData.experience.get(index).add(Asker.Answer.valueOf(temp));//text = text + temp + " ";
				column++;
			}while (!temp.equals("#"));
			
			row++;
			column = 0;
			rown = sheet.getRow(row);
			cell = rown.getCell(column);
			temp = cell.getStringCellValue();
			
			index++;
		} while (!temp.equals("#"));
        
        return qData;
	}
	
	public static void convertQDataToExcel(QuestionData qData, String excelFile) throws IOException{
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sheet 1");
		int row = 0;
		int column = 1;
		HSSFRow rown = sheet.createRow(row);
		HSSFCell cell = rown.createCell(column);
		
		
		
		
		for(int i = 0;i<qData.questions.size();i++){
			cell.setCellValue(qData.questions.get(i));
			column += 1;
			cell = rown.createCell(column);
		}
		cell.setCellValue("#");
		
		column = 0;
		row = 1;
		rown = sheet.createRow(row);
		cell = rown.createCell(column);
		
		for(int i = 0;i<qData.answers.size();i++){
			cell.setCellValue(qData.answers.get(i));
			row += 1;
			rown = sheet.createRow(row);
			cell = rown.createCell(column);
		}
		cell.setCellValue("#");
		
		
		column = 1;
		row = 1;
		rown = sheet.getRow(row);
		cell = rown.createCell(column);
		
		for(int i=0;i<qData.experience.size();i++){
			for(int j=0;j<qData.experience.get(0).size();j++){
				cell.setCellValue(qData.experience.get(i).get(j).toString());//(answers[i]);
				column += 1;
				cell = rown.createCell(column);
			}
			cell.setCellValue("#");
			row += 1;
			column = 1;
			rown = sheet.getRow(row);
			cell = rown.createCell(column);
		}
		
		for(int j=0;j<=qData.experience.get(0).size();j++){
			cell.setCellValue("#");
			column += 1;
			cell = rown.createCell(column);
		}
		
		workbook.write(new FileOutputStream(excelFile));
	}
}
